﻿

CREATE PROCEDURE [dbo].[fares_GenereazaD394Facturi]
	@CodUnitate smallint,
	@DataStart datetime,
	@DataEnd datetime,
	@cumparari bit,
	@taxareinversa bit, ---cele cu taxare inversa separat
	@exprExcludere varchar(20),
	@total decimal(20,0) OUT,	
	@totaltva decimal(20,0) OUT,
	@nrFact decimal(20,0) OUT		
AS
BEGIN
	SET NOCOUNT ON;
	SELECT @total = sum(TotalValoare),@totaltva=SUM(TotalValoareTVA) FROM 
	(SELECT ROUND(Sum(c.ValoareTotala),0) as TotalValoare, ROUND(sum(c.ValoareTVA),0)  as TotalValoareTVA FROM 
	[Documente] a JOIN Parteneri b ON a.IDPartener = b.IDPartener JOIN
	[DocumenteDetaliu] c ON a.[IDDocument]=c.IDDocument
	JOIN [RegimFiscal] r ON c.IDRegimFiscal = r.IDRegimFiscal
	WHERE a.[CodUnitate]=@CodUnitate AND
	DataDoc>= @DataStart AND DataDoc<=@DataEnd  AND RTRIM(LTRIM(b.[AtributFiscal]))='RO'
	AND ((@cumparari=1 AND a.IDTipDocument=0) OR (@cumparari=0 AND a.IDTipDocument=1))
	AND r.IntraIn394=1
	AND  ((@taxareinversa=0 AND c.IDRegimFiscal<>6) OR (@taxareinversa=1 AND c.IDRegimFiscal=6 AND @cumparari=1))
	GROUP BY a.IDPartener) a
	
	--numara facturile de un anumit tip cu exceptia celor
	--care au numarul diferit de expresia de excludere	
	SELECT @NrFact = COUNT(DISTINCT REPLACE(NrDoc,@exprExcludere,'')+CAST(DataDoc AS VARCHAR(20))) FROM 
	[Documente] a JOIN Parteneri b ON a.IDPartener = b.IDPartener JOIN
	[DocumenteDetaliu] c ON a.[IDDocument]=c.IDDocument
	JOIN [RegimFiscal] r ON c.IDRegimFiscal = r.IDRegimFiscal
	WHERE a.[CodUnitate]=@CodUnitate AND
	DataDoc>= @DataStart AND DataDoc<=@DataEnd  AND RTRIM(LTRIM(b.[AtributFiscal]))='RO'
	AND ((@cumparari=1 AND a.IDTipDocument=0) OR (@cumparari=0 AND a.IDTipDocument=1))
	AND r.IntraIn394=1
	AND  ((@taxareinversa=0 AND c.IDRegimFiscal<>6) OR (@taxareinversa=1 AND c.IDRegimFiscal=6 AND @cumparari=1))
	--AND (LTRIM(RTRIM(@exprExcludere))='' OR (NrDoc NOT LIKE '%' +@exprExcludere+ '%'))
    
	SELECT MAX(b.CUI) as CUI,MAX(b.Denumire) as Denumire,
	ROUND(Sum(c.ValoareTotala),0) as TotalValoare, ROUND(sum(c.ValoareTVA),0)  as TotalValoareTVA,
    COUNT(DISTINCT REPLACE(NrDoc,@exprExcludere,'')+CAST(DataDoc AS VARCHAR(20))) NrFact
	FROM 
	[Documente] a JOIN Parteneri b ON a.IDPartener = b.IDPartener JOIN
	[DocumenteDetaliu] c ON a.[IDDocument]=c.IDDocument
	JOIN [RegimFiscal] r ON c.IDRegimFiscal = r.IDRegimFiscal
	WHERE a.[CodUnitate]=@CodUnitate AND
	DataDoc>= @DataStart AND DataDoc<=@DataEnd  AND RTRIM(LTRIM(b.[AtributFiscal]))='RO'
	AND ((@cumparari=1 AND a.IDTipDocument=0) OR (@cumparari=0 AND a.IDTipDocument=1))
	AND r.IntraIn394=1
	AND  ((@taxareinversa=0 AND c.IDRegimFiscal<>6) OR (@taxareinversa=1 AND c.IDRegimFiscal=6))
	GROUP BY a.IDPartener
END